﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using DotNetCommon;

namespace DBUtil.Provider.MySql
{
    public partial class MySqlAccess : DBAccess
    {

        #region Is系列: 判断表/视图/列/存储过程是否存在
        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async override Task<bool> IsProcedureExistAsync(string procName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"show PROCEDURE status where Db ='{DataBase}' and name='{DealSqlInject(ParseObjectName(procName).Name)}' and `type` = 'PROCEDURE'";
            var dt = await SelectDataTableAsync(sql);
            return dt.Rows.Count > 0;
        }

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async override Task<bool> IsTriggerExistAsync(string triggerName, CancellationToken cancellationToken = default(CancellationToken))
        {
            var sql = $"select count(1) from information_schema.TRIGGERS t where t.TRIGGER_SCHEMA = '{DataBase}' and TRIGGER_NAME='{DealSqlInject(ParseObjectName(triggerName).Name)}'";
            return await SelectScalarAsync<int>(sql, cancellationToken) > 0;
        }
        #endregion

        #region 基于数据库的Id和流水号生成器
        /// <summary>
        /// 根据指定的表名和列名生成Id
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected override async Task<long> InternalNewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default)
        {
            EnsureInitGenerator();
            tableName = ParseObjectName(tableName).NameQuoted;
            colName = ParseObjectName(colName).NameQuoted;
            var id = await SelectScalarAsync<long>($"call {Settings.DBCacheGeneratorIdProcedureName} (\"{tableName}\",\"{colName}\",1)", cancellationToken);
            return id;
        }

        protected override async Task<List<long>> InternalNewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            EnsureInitGenerator();
            tableName = ParseObjectName(tableName).NameQuoted;
            colName = ParseObjectName(colName).NameQuoted;
            var id = await SelectScalarAsync<long>($"call {Settings.DBCacheGeneratorIdProcedureName} (\"{tableName}\",\"{colName}\",{count})", cancellationToken);
            var res = new List<long>();
            for (long i = id - count + 1; i <= id; i++)
            {
                res.Add(i);
            }
            return res;
        }

        protected override async Task<string> InternalNewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default)
        {
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            tableName = ParseObjectName(tableName).NameQuoted;
            colName = ParseObjectName(colName).NameQuoted;
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
call {Settings.DBCacheGeneratorSNOProcedureName}(
    '{tableName}',
    '{colName}',
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
1)";
            long no = await SelectScalarAsync<long>(sql, cancellationToken);

            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var sno = likestr.Substring(0, startindex);
            var s = no.ToString();
            if (s.Length > chunk.Length)
            {
                sno += s;
            }
            else
            {
                sno += s.PadLeft(chunk.Length, '0');
            }
            return sno;
        }

        protected override async Task<List<string>> InternalNewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default)
        {
            if (count < 1) throw new Exception("批量生成的数量最小为1!");
            SerialFormat.ValidFormat(format);
            EnsureInitGenerator();
            tableName = ParseObjectName(tableName).NameQuoted;
            colName = ParseObjectName(colName).NameQuoted;
            var now = DateTime.Now;
            var (likestr, snoNow, startindex, statictext, machineIdString) = PrePareForSNO(format, now, DotNetCommon.Machine.MachineIdString);
            var sql = $@" 
call {Settings.DBCacheGeneratorSNOProcedureName}(
    '{tableName}',
    '{colName}',
    '{statictext}',
    '{machineIdString}',
    '{snoNow.ToString(format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.DateText).FormatString)}',
    '{likestr}',
    {startindex},
{count})";

            long no = long.Parse(await SelectScalarAsync<string>(sql, cancellationToken));
            var chunk = format.Chunks.FirstOrDefault(i => i.Type == SerialFormatChunkType.SerialNo);
            var res = new List<string>();
            for (long i = no - count + 1; i <= no; i++)
            {
                var sno = likestr.Substring(0, startindex);
                var s = i.ToString();
                if (s.Length > chunk.Length)
                {
                    sno += s;
                }
                else
                {
                    sno += s.PadLeft(chunk.Length, '0');
                }
                res.Add(sno);
            }
            return res;
        }
        #endregion
    }
}
